package org.lanlab.storode.dal.repository;

import org.lanlab.storode.dal.entity.Page;
import org.lanlab.storode.dal.entity.Requirement;
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * @author dove
 * @since 2020/1/9 14:19
 */
public interface PageRepository extends CrudRepository<Page, Long> {

    @Query("SELECT r.id, r.requirement_no, r.name, r.version, r.description, r.state, r.create_time, " +
            "r.update_time, r.is_deleted FROM requirement r, page_requirement pr " +
            "WHERE pr.page_id = :pageId AND r.requirement_no = pr.requirement_no AND " +
            "r.version = (SELECT max(rs.version) FROM requirement rs WHERE r.requirement_no = rs.requirement_no)")
    List<Requirement> listRequirementsByPageId(@Param("pageId") Long pageId);

    @Query("INSERT INTO page_requirement (page_id, requirement_no) values (:pageId, :requirementNo)")
    boolean savePageRequirement(@Param("pageId") Long pageId, @Param("requirementNo") String requirementNo);

    @Query("SELECT * FROM page WHERE parent_id = :parentId")
    List<Page> listChildrenPages(@Param("parentId") Long parentId);
}
